Configuring: Relational Merge

Select the two datasets that you want to merge at the points of relation - i.e. where the values are common to the mapped fields of both datasets. Configure which fields from each component dataset will be included in the merged dataset, and how you wish to present the merged dataset.

Worked example: Relational merge

Steps to configure

  1. Merge fields: Map fields from the component datasets that you will select for the merging operation. Merging will take place on each value that is common to each of these mapped fields. Conversely, each field that is mapped adds another criterion that needs to be met (i.e. the data values need to be common to both fields) in order for those fields to be merged.

  2. Merge type: Select how the datasets must be merged, which will affect the number of rows included in the newly created dataset.

  3. First dataset's fields: Select the fields from the first dataset that you want to include in the new dataset.

  4. Second dataset's fields: Select the fields from the second dataset that you want to include in the new dataset.

  5. Execution: A new dataset will be created, and will show the fields and rows specified during configuration.

Step 1: Merge fields

Map fields from the component datasets that you will select for the merging operation. Merging will take place on each value that is common to each of these mapped fields. Conversely, each field that is mapped adds another criterion that needs to be met (i.e. the data values need to be common to both fields) in order for those fields to be merged.

  • Map fields that you know will have values in common.

  • Fields from one dataset can only be matched to fields of the other dataset if they are of the same data type.

  • Fields from the first dataset will be listed in the left windows, to be associated with fields from the second dataset listed in the right window.

  • Highlight the field on the left, then the relevant field on the right and use the [<] key to add this field in the associated [column].

  • Default: no fields are selected.

  • Minimum configuration: at least one pair of fields must be mapped.

Step 2: Merge type

Select how the datasets must be merged, which will affect the rows included in the newly created dataset. To configure these merged results, select an option from the list provided. The diagrams alongside the radio buttons on the configuration page illustrate which parts of the merged datasets will be included the newly created dataset.

  • Default: [Matched rows] is selected.

Matched rows:

  • The final dataset will only contain rows where data was common in the mapped fields of both original datasets.

  • Matched rows will be included for each occurrence of common values.

  • All mapping criteria need to be met in order for merging to occur at the matched rows.

Unmatched rows:

  • Select one of three further options:

    • [All]: all rows with no matching criteria will be included in the merged dataset. This effectively excludes all the common values in mapped fields.

    • [First Table Only]: rows from only the first dataset where the merge criteria were NOT met will be included.

    • [Second Table Only]: rows from only the second dataset where the merge criteria were NOT met will be included.

All rows:

  • This shows all matching rows and all unmatched rows in the final dataset.

Step 3: Select first dataset's fields

Select fields from the first dataset that should be included in the created dataset.

  • Including these specified fields has no influence on the merging operation.

  • It simply includes the data in the selected fields in the created dataset.

  • Default: all fields are selected.

Step 4: Select second dataset's fields

Select fields from the second dataset that should be included in the created dataset.

  • Including these specified fields has no influence on the merging operation.

  • It simply includes the data in the selected fields in the created dataset.

  • Note that if a field with the same name as the chosen field was already selected from the first dataset, a “_1” will be appended to the final field. If that field also exists, a higher number will be appended that would make the field’s name unique.

  • Default: all fields are selected.

Step 5: Execution

A new dataset is created, containing rows and fields as specified during configuration. Mapped fields from both component datasets with common values will be merged at the point of relation. How the created dataset is presented is determined during configuration.


Related topics:

  

CSense 2023- Last updated: June 24,2025